-- @owner: @haomeng802
-- @date: 2023/1/16
-- @testpoint: 打开query_dop后,执行含/*+ choose_adaptive_gplan */的pbe查询,查询语句为connect by
-- @modify: modified by @kyeleze at 2023/05/24 代码变更，优化用例期望
-- @modify: modified by @haomeng at 2025/03/17 代码变更，优化用例期望

--step1:设置并行度为2;expect:成功
set query_dop=2;

--step2:创建表并插入数据;expect:成功
drop table if exists t_deallocate_007_1;
drop table if exists t_deallocate_007_2;
create table t_deallocate_007_1(a1 int, a2 int);
create table t_deallocate_007_2(b1 int, b2 int);

insert into t_deallocate_007_1 values(1,3);
insert into t_deallocate_007_1 values(2,4);
insert into t_deallocate_007_1 values(4,1);
insert into t_deallocate_007_1 values(5,2);
insert into t_deallocate_007_1 values(8,5);
insert into t_deallocate_007_1 values(9,8);
insert into t_deallocate_007_1 values(10,9);
insert into t_deallocate_007_1 values(12,10);
insert into t_deallocate_007_1 values(12,10);
insert into t_deallocate_007_1 values(1,12);

insert into t_deallocate_007_2 values(2,1);
insert into t_deallocate_007_2 values(3,2);
insert into t_deallocate_007_2 values(4,3);
insert into t_deallocate_007_2 values(5,4);
insert into t_deallocate_007_2 values(6,5);
insert into t_deallocate_007_2 values(7,6);

--step3:创建预备语句,含pbe查询和递归查询;expect:成功
prepare pre_0007 as select /*+ choose_adaptive_gplan */ * from t_deallocate_007_1, t_deallocate_007_2 where a1+1=b1 and a1<$1 start with a1=$2 connect by a1=prior b1;

--step4:执行预备语句;expect:成功
execute pre_0007(10,1);
execute pre_0007(10,1);
execute pre_0007(10,1);
execute pre_0007(10,1);
execute pre_0007(8,1);
execute pre_0007(9,1);

--step5:清理环境;expect:成功
deallocate pre_0007;
drop table t_deallocate_007_1 cascade;
drop table t_deallocate_007_2 cascade;
